package dataBase;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import servicios.GestorServicio;
import servicios.Servicio;

import clientes.Cliente;
import delegaciones.Delegacion;

import auxiliar.Turno;

import empleados.*;

public class JDBCPermutaDAO {
	private Connection conn;
	
	public JDBCPermutaDAO(){
		conn = ConnectionManager.getInstance().checkOut();
	}
	
	public void finalice(){
		ConnectionManager.getInstance().checkIn(conn);
	}
	
	//crear una permuta nueva
	public void insertPermuta(Permuta p) {
		Connection conn = ConnectionManager.getInstance().checkOut();

		String sql = "INSERT INTO permutas (aceptada, DNIempA, DNIempB, DNIadmin, consentimiento_emp, hora_ini_TA, hora_fin_TA, hora_ini_TB, hora_fin_TB, nombre_servicio, dni_cliente, dia) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);

            stmt.setString(1, p.getAceptada());
            stmt.setString(2, p.getDNI1());
            stmt.setString(3, p.getDNI2());
            stmt.setString(4, p.getDNIadmin());
            stmt.setString(5, p.getConsentimiento_emp());
            stmt.setTime(6, p.getTurno1().getHora_ini());
            stmt.setTime(7, p.getTurno1().getHora_fin());
            stmt.setTime(8, p.getTurno2().getHora_ini());
            stmt.setTime(9, p.getTurno2().getHora_fin());
            stmt.setString(10, p.getServicio().getNombreServicio());
            stmt.setString(11, p.getServicio().getCIF_cliente());
            stmt.setDate(12, p.getDia());
            stmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
	}
	
	//Borra una permuta
	public void deletePermuta(Integer id){
		
		String sql = "DELETE FROM permutas WHERE (Id_permuta = ?) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, id);
            stmt.executeUpdate();
        } catch (SQLException e) {
            System.err.println("Message: " + e.getMessage());
            System.err.println("SQLState: " + e.getSQLState());
            System.err.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
	}
	
	//actualiza una permuta con el dni y la confirmacion que le pasa el administrador
	public void updatePermuta(int idPermuta,String dnia, String aceptado){
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		ResultSet result = null;
		String sql = "UPDATE permutas SET aceptada = ?,DNIadmin = ?" +"WHERE (Id_permuta = ?)";
				
		
		try{
			stmt = conn.prepareStatement(sql);
			
			stmt.setString(1, aceptado);
			stmt.setString(2, dnia);
            stmt.setInt(3, idPermuta);
		
			stmt.executeUpdate();
		}catch(SQLException ex){
			System.out.println("Message: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("ErrorCode: " + ex.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ex) {
            }
        }
	}
	//actualiza una permuta con la confirmacion que le pasa el usuario 2
	public void updatePermutaEmp2(int idPermuta, String aceptado){
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		ResultSet result = null;
		String sql = "UPDATE permutas SET consentimiento_emp = ?" +"WHERE (Id_permuta = ?)";
				
		
		try{
			stmt = conn.prepareStatement(sql);
			
			stmt.setString(1, aceptado);
            stmt.setInt(2, idPermuta);
		
			stmt.executeUpdate();
		}catch(SQLException ex){
			System.out.println("Message: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("ErrorCode: " + ex.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ex) {
            }
        }
	}
	//muestra una permuta a partir de su id
	public Permuta selectPermuta(Integer id) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
    	Permuta p = new Permuta();
    	Turno t1 = new Turno();
    	Turno t2 = new Turno();
    	Servicio s = new Servicio();
       
        String sql = "SELECT * FROM permutas WHERE (Id_permuta = ?) ";
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, id);
            result = stmt.executeQuery();

            if(result.next()){
            	p.setAceptada(result.getString("aceptada"));
            	p.setDNI1(result.getString("DNIempA"));
            	p.setDNI2(result.getString("DNIempB"));
            	p.setDNIadmin(result.getString("DNIadmin"));
            	p.setConsentimiento_emp(result.getString("consentimiento_emp"));
            	p.setIdPermuta(result.getInt("Id_permuta"));
            	t1.setHora_ini(result.getTime("hora_ini_TA"));
            	t1.setHora_fin(result.getTime("hora_fin_TA"));
            	t2.setHora_ini(result.getTime("hora_ini_TB"));
            	t2.setHora_fin(result.getTime("hora_fin_TB"));
            	s.setNombreServicio(result.getString("nombre_servicio"));
            	s.setCIF_cliente(result.getString("dni_cliente"));
            	p.setTurno1(t1);
            	p.setTurno2(t2);
            	p.setServicio(s);
            	p.setDia(result.getDate("dia"));
            }
        } catch (SQLException u) {
            System.out.println("Message: " + u.getMessage());
            System.out.println("SQLState: " + u.getSQLState());
            System.out.println("ErrorCode: " + u.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException u) {
            }
        }
        return p;
	}
	
	//devuelve una lista de todos las permutas que ha solicitado un empleado
	public List<Permuta> selectPermutaEmp1(String dni){
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
        
        List<Permuta> lp = new LinkedList<Permuta>();
        Permuta p = new Permuta();
        Turno t1 = new Turno();
    	Turno t2 = new Turno();
    	Servicio s = new Servicio();
        String sql = "SELECT * FROM permutas WHERE (DNIempA = ?) ORDER BY consentimiento_emp,aceptada";
    
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, dni);
            
            result = stmt.executeQuery();

            while (result.next()) {
            	p=new Permuta();
            	p.setAceptada(result.getString("aceptada"));
            	p.setDNI1(result.getString("DNIempA"));
            	p.setDNI2(result.getString("DNIempB"));
            	p.setDNIadmin(result.getString("DNIadmin"));
            	p.setConsentimiento_emp(result.getString("consentimiento_emp"));
            	p.setIdPermuta(result.getInt("Id_permuta"));
            	t1.setHora_ini(result.getTime("hora_ini_TA"));
            	t1.setHora_fin(result.getTime("hora_fin_TA"));
            	t2.setHora_ini(result.getTime("hora_ini_TB"));
            	t2.setHora_fin(result.getTime("hora_fin_TB"));
            	s.setNombreServicio(result.getString("nombre_servicio"));
            	s.setCIF_cliente(result.getString("dni_cliente"));
            	p.setTurno1(t1);
            	p.setTurno2(t2);
            	p.setServicio(s);
            	p.setDia(result.getDate("dia"));
                lp.add(p);
            }
        } catch (SQLException ex) {
            System.out.println("Message: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("ErrorCode: " + ex.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ex) {
            }
        }
        return lp;
	}
	
	//devuelve una lista de todos las permutas que le han solicitado a un empleado con un estado en consentimiento_emp pasado por parametro
	public List<Permuta> selectPermutaEmp2(String dni, String consentimiento){
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
        
        List<Permuta> lp = new LinkedList<Permuta>();
        Permuta p = new Permuta();
        Turno t1 = new Turno();
    	Turno t2 = new Turno();
    	Servicio s = new Servicio();
        String sql = "SELECT * FROM permutas WHERE (DNIempB = ? AND consentimiento_emp =? ) ORDER BY aceptada";
    
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, dni);
            stmt.setString(2, consentimiento);
            result = stmt.executeQuery();

            while (result.next()) {
            	p=new Permuta();
            	p.setAceptada(result.getString("aceptada"));
            	p.setDNI1(result.getString("DNIempA"));
            	p.setDNI2(result.getString("DNIempB"));
            	p.setDNIadmin(result.getString("DNIadmin"));
            	p.setConsentimiento_emp(result.getString("consentimiento_emp"));
            	p.setIdPermuta(result.getInt("Id_permuta"));
            	t1.setHora_ini(result.getTime("hora_ini_TA"));
            	t1.setHora_fin(result.getTime("hora_fin_TA"));
            	t2.setHora_ini(result.getTime("hora_ini_TB"));
            	t2.setHora_fin(result.getTime("hora_fin_TB"));
            	s.setNombreServicio(result.getString("nombre_servicio"));
            	s.setCIF_cliente(result.getString("dni_cliente"));
            	p.setTurno1(t1);
            	p.setTurno2(t2);
            	p.setServicio(s);
            	p.setDia(result.getDate("dia"));
                lp.add(p);
            }
        } catch (SQLException ex) {
            System.out.println("Message: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("ErrorCode: " + ex.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ex) {
            }
        }
        return lp;
	}
	
	//devuelve una lista de todos las permutas que le han solicitado a un empleado 
	public List<Permuta> selectPermutaEmp2(String dni){
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
        
        List<Permuta> lp = new LinkedList<Permuta>();
        Permuta p = new Permuta();
        Turno t1 = new Turno();
    	Turno t2 = new Turno();
    	Servicio s = new Servicio();
        String sql = "SELECT * FROM permutas WHERE (DNIempB = ? ) ORDER BY aceptada,consentimiento_emp";
    
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, dni);
           
            result = stmt.executeQuery();

            while (result.next()) {
            	p=new Permuta();
            	p.setAceptada(result.getString("aceptada"));
            	p.setDNI1(result.getString("DNIempA"));
            	p.setDNI2(result.getString("DNIempB"));
            	p.setDNIadmin(result.getString("DNIadmin"));
            	p.setConsentimiento_emp(result.getString("consentimiento_emp"));
            	p.setIdPermuta(result.getInt("Id_permuta"));
            	t1.setHora_ini(result.getTime("hora_ini_TA"));
            	t1.setHora_fin(result.getTime("hora_fin_TA"));
            	t2.setHora_ini(result.getTime("hora_ini_TB"));
            	t2.setHora_fin(result.getTime("hora_fin_TB"));
            	s.setNombreServicio(result.getString("nombre_servicio"));
            	s.setCIF_cliente(result.getString("dni_cliente"));
            	p.setTurno1(t1);
            	p.setTurno2(t2);
            	p.setServicio(s);
            	p.setDia(result.getDate("dia"));
                lp.add(p);
            }
        } catch (SQLException ex) {
            System.out.println("Message: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("ErrorCode: " + ex.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ex) {
            }
        }
        return lp;
	}
	//devuelve una lista de todos las permutas que tienen un estado en aceptada pasado por parametro
	public List<Permuta> selectPermutaEstadoAceptada(String estado){
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
        
        List<Permuta> lp = new LinkedList<Permuta>();
        Permuta p = new Permuta();
        Turno t1 = new Turno();
    	Turno t2 = new Turno();
    	Servicio s = new Servicio();
        String sql = "SELECT * FROM permutas WHERE (aceptada = ?) ORDER BY consentimiento_emp";
    
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, estado);
            
            result = stmt.executeQuery();

            while (result.next()) {
            	p=new Permuta();
            	p.setAceptada(result.getString("aceptada"));
            	p.setDNI1(result.getString("DNIempA"));
            	p.setDNI2(result.getString("DNIempB"));
            	p.setDNIadmin(result.getString("DNIadmin"));
            	p.setConsentimiento_emp(result.getString("consentimiento_emp"));
            	p.setIdPermuta(result.getInt("Id_permuta"));
            	t1.setHora_ini(result.getTime("hora_ini_TA"));
            	t1.setHora_fin(result.getTime("hora_fin_TA"));
            	t2.setHora_ini(result.getTime("hora_ini_TB"));
            	t2.setHora_fin(result.getTime("hora_fin_TB"));
            	s.setNombreServicio(result.getString("nombre_servicio"));
            	s.setCIF_cliente(result.getString("dni_cliente"));
            	p.setTurno1(t1);
            	p.setTurno2(t2);
            	p.setServicio(s);
            	p.setDia(result.getDate("dia"));
                lp.add(p);
            }
        } catch (SQLException ex) {
            System.out.println("Message: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("ErrorCode: " + ex.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ex) {
            }
        }
        return lp;
	}
	
	
	
	
	
}
